package org.qianji.goosql.test;

import java.util.List;

import org.qianji.goosql.cond.Conditions;
import org.qianji.goosql.dql.QueryStatement;
import org.qianji.goosql.field.Field;
import org.qianji.goosql.table.SimpleTable;
import org.qianji.goosql.table.SubQueryTable;
import org.qianji.goosql.table.Table;

/**
 * SQL查询语句测试类。
 * 
 * @author gmz
 * 
 */
public class QueryStatementTester {

	/**
	 * 测试简单查询。
	 */
	public void simpleQuery() {
		Table tb = new SimpleTable("test").as("t");
		Field f1 = tb.field("f1");
		QueryStatement qs = new QueryStatement().select(f1, tb.field("f2")).from(tb).where(Conditions.eq(f1, 100));
		System.out.println("simple query: ");
		System.out.println(qs.toSql());
		List<Object> parl = qs.getPlacedParameters();
		for (Object par : parl) {
			System.out.println(par.toString());
		}
	}

	/**
	 * 测试关联查询。
	 */
	public void joinQuery() {
		Table tb = new SimpleTable("test").as("t");
		Table t2 = new SimpleTable("test2").as("t2");
		Field f1 = tb.field("f1");
		Field f2 = tb.field("f2"), t2f1 = t2.field("t2f1");
		QueryStatement qs = new QueryStatement().select(f1, f2, t2f1).from(tb)
				.leftJoin(t2, Conditions.eq(f1, t2f1).or(Conditions.eq(f1, "f1-on")));
		qs.where(Conditions.eq(f1, "f1-value"), Conditions.ge(tb.field("f3"), "f3-value")).orderBy(f1, false);
		qs.where(Conditions.in(t2f1, new Object[] { "val1", "val2", "val3" }));
		System.out.println("join query: ");
		System.out.println(qs.toSql());
		List<Object> parl = qs.getPlacedParameters();
		for (Object par : parl) {
			System.out.println(par.toString());
		}
	}

	/**
	 * 测试子查询。
	 */
	public void subQuery() {
		Table t1 = new SimpleTable("test1").as("t1");
		Table t2 = new SimpleTable("test2").as("t2");
		QueryStatement qs2 = new QueryStatement().select().from(t2).where(Conditions.gt(t2.field("f1"), 100.50))
				.orderBy(t2.field("id"), false);
		Table temp = new SubQueryTable(qs2, "t2");
		QueryStatement qs = new QueryStatement().select(t1.field("f1"), temp.field("f2")).from(t1)
				.innerJoin(temp, Conditions.eq(t1.field("f1"), temp.field("f1")));
		System.out.println(qs.toSql());
		for (Object par : qs.getPlacedParameters()) {
			System.out.println(par.toString());
		}
	}

	/**
	 * 测试IN查询。
	 */
	public void inSubQuery() {
		Table t1 = new SimpleTable("test1").as("t1");
		Table t2 = new SimpleTable("test2").as("t2");
		QueryStatement subQuery = new QueryStatement().select(t2.field("f1")).from(t2)
				.where(Conditions.eq(t2.field("f2"), "f2-value"));
		QueryStatement qs = new QueryStatement().selectNew().from(t1).where(Conditions.in(t1.field("f1"), subQuery));
		System.out.println(qs.toSql());
		for (Object par : qs.getPlacedParameters()) {
			System.out.println(par.toString());
		}
	}

	public static void main(String[] args) {
		QueryStatementTester qst = new QueryStatementTester();
		qst.simpleQuery();
		System.out.println();
		qst.joinQuery();
		System.out.println();
		qst.subQuery();
		System.out.println();
		qst.inSubQuery();
		System.out.println();
	}

}
